from datetime import date, datetime, timedelta

from flask import g, jsonify, request, send_file

from sqlalchemy import func

from .core import app
from .exceptions import AabException
from .utils import login_required, success, jsonify_error, SheetData

from .model import Dwdm, Zwdm, Roles, db, User, Ztdm, Ry, Zwinfo, Jcz, Xjinfo, Friend, Gld


@app.route('/home/stats', methods=['GET'])
@login_required
def query_home_stats():
    return jsonify({
        'ry_total_count': Ry.query.count()
    })


@app.route("/departments", methods=['GET'])
@login_required(roles=Roles.DEPARTMENT_USERS, need_user=True)
def query_departments():
    if g.auth.role == Roles.DEPARTMENT_MANAGER:
        dm_prefix = g.user.department.dm[:2]
        result = Dwdm.query.filter(Dwdm.dm.like(dm_prefix + '%')).order_by(Dwdm.dm)
    else:
        result = Dwdm.query.order_by(Dwdm.dm)
    return jsonify([department.to_dict() for department in result])


@app.route('/department/<dm>', methods=['PUT'])
@login_required(roles=Roles.DEPARTMENT_USERS, need_user=True)
def add_department(dm):
    data = request.get_json()
    if data is None:
        raise AabException(400, message='Require JSON data')

    if g.auth.role == Roles.DEPARTMENT_MANAGER:
        dm_prefix = g.user.department.dm[:2]
        if not dm.startswith(dm_prefix):
            raise AabException(400, message='DM Forbidden')

    department = Dwdm.query.filter_by(dm=dm).scalar()
    if department is None:
        department = Dwdm(dm=dm).update_from_dict(data)
        db.session.add(department)
    else:
        department.update_from_dict(data)
    db.session.commit()
    return success()


@app.route('/department/<dm>', methods=['DELETE'])
@login_required(roles=Roles.DEPARTMENT_USERS, need_user=True)
def delete_department(dm):
    check_department(dm)

    department = Dwdm.query.filter_by(dm=dm).scalar()
    if department is not None:
        db.session.delete(department)
        db.session.commit()

    return success()


@app.route('/my-department', methods=['GET'])
@login_required(need_user=True)
def query_department():
    department = g.user.department
    data = department.to_dict()

    zw_dict = {item.mc: {"count": 0, "dm": item.dm} for item in Zwdm.query.all()}
    today = date.today()
    for zw in Zwinfo.query.filter_by(day=today, dw=department):
        zw_dict[zw.lx.mc]['count'] = zw.sl

    data['zw_list'] = [{'lx': k, 'sl': v['count'], 'dm': v['dm']} for k, v in zw_dict.items()]
    return jsonify(data)


@app.route('/ztdms', methods=['GET'])
@login_required
def query_ztdms():
    return jsonify([item.to_dict() for item in Ztdm.query.all()])


@app.route('/ztdm/<dm>', methods=['DELETE'])
@login_required
def delete_ztdm(dm):
    leave = Ztdm.query.filter_by(dm=dm).scalar()
    if leave is not None:
        db.session.delete(leave)
        db.session.commit()
    return success()


@app.route('/ztdm/<dm>', methods=['PUT'])
@login_required
def add_ztdm(dm):
    data = request.get_json()
    if data is None:
        return jsonify_error(400, message='Require JSON data')

    ztdm = Ztdm.query.filter_by(dm=dm).scalar()
    if ztdm is None:
        ztdm = Ztdm(dm=dm).update_from_dict(data)
        db.session.add(ztdm)
    else:
        ztdm.update_from_dict(data)

    db.session.commit()
    return success()


@app.route('/zwdms', methods=['GET'])
@login_required()
def query_all_ztdm():
    return jsonify([d.to_dict() for d in Zwdm.query.order_by(Zwdm.mc)])


@app.route('/zwdm/<dm>', methods=['DELETE'])
@login_required
def delete_zwdm(dm):
    leave = Zwdm.query.filter_by(dm=dm).scalar()
    if leave is not None:
        db.session.delete(leave)
        db.session.commit()
    return success()


@app.route('/zwdm/<dm>', methods=['PUT'])
@login_required(roles=Roles.DEPARTMENT_USERS)
def add_zwdm(dm):
    data = request.get_json()
    if data is None:
        return jsonify_error(400, message='Require JSON data')

    zwdm = Zwdm.query.filter_by(dm=dm).scalar()
    if zwdm is None:
        zwdm = Zwdm(dm=dm).update_from_dict(data)
        db.session.add(zwdm)
    else:
        zwdm.update_from_dict(data)

    db.session.commit()
    return success()


@app.route('/rys', methods=['GET'])
@login_required(roles=Roles.DEPARTMENT_USERS, need_user=True)
def query_rys():
    if g.auth.role == Roles.DEPARTMENT_MANAGER:
        dm_prefix = g.user.department.dm[:2]
        leaves = [_ry_to_dict(leave) for leave in Ry.query.filter(Ry.dwdm.like(dm_prefix + '%')).order_by(Ry.sfzh)]
    else:
        leaves = [_ry_to_dict(leave) for leave in Ry.query.order_by(Ry.sfzh)]
    return jsonify(leaves)


@app.route('/ry/<sfzh>', methods=['GET'])
@login_required(roles=Roles.DEPARTMENT_USERS)
def query_ry(sfzh):
    ry = Ry.query.filter_by(sfzh=sfzh).scalar()
    if ry is None:
        return jsonify(None)
    else:
        return jsonify(_ry_to_dict2(ry))


def _ry_to_dict(ry):
    result = ry.to_dict()
    result['persons'] = [item.to_dict() for item in ry.jczs]
    result['routes'] = [item.to_dict() for item in ry.xjinfos]
    result['friends'] = [item.to_dict() for item in ry.friends]
    return result

def _ry_to_dict2(ry):
    result = ry.to_dict()
    result['persons'] = [item.to_dict() for item in ry.jczs]
    result['routes'] = [item.to_dict() for item in ry.xjinfos]
    if len(ry.xjinfos):
        xjinfo = ry.xjinfos[0]
        result['route'] = xjinfo.to_dict()
    else:
        result['route'] = {}


    result['friends'] = [item.to_dict() for item in ry.friends]
    return result

@app.route('/ry/<sfzh>', methods=['DELETE'])
@login_required(need_user=True)
def delete_ry(sfzh):
    ry = Ry.query.filter_by(sfzh=sfzh).scalar()
    if ry is not None:
        check_department(ry.dwdm)
        for exist in ry.xjinfos:
            db.session.delete(exist)
        db.session.delete(ry)
        db.session.commit()
    return success()


@app.route('/ry/<sfzh>', methods=['PUT'])
@login_required(need_user=True)
def fill_ry(sfzh):
    data = request.get_json()
    if data is None:
        raise AabException(400, message='Require JSON data')

    ry = Ry.query.filter_by(sfzh=sfzh).scalar()
    if ry is None:
        ry = Ry(sfzh=sfzh)
        db.session.add(ry)
    else:
        check_department(ry.dwdm)

    update_ry(data, ry)

    db.session.commit()
    return success()


def update_ry(data, ry):
    ry.update_from_dict(data)

    if 'route' in data:
        # xjinfos = Xjinfo.query.filter_by(ry=ry)
        xjinfos = list(ry.xjinfos)
        if len(xjinfos):
            xjinfo = xjinfos[0].update_from_dict(data['route'])
        else:
            xjinfo = Xjinfo(ry=ry).update_from_dict(data['route'])
        db.session.add(xjinfo)


    if 'persons' in data:
        exists = Jcz.query.filter_by(ry=ry)
        for exist in exists:
            db.session.delete(exist)
        for person in data['persons']:
            co_person = Jcz(ry=ry).update_from_dict(person)
            db.session.add(co_person)

    # if 'routes' in data:
    #     exists = Xjinfo.query.filter_by(ry=ry)
    #     for exist in exists:
    #         db.session.delete(exist)
    #     for route in data['routes']:
    #         db.session.add(Xjinfo(ry=ry).update_from_dict(route))

    if 'friends' in data:
        exists = Friend.query.filter_by(ry=ry)
        for exist in exists:
            db.session.delete(exist)
        for friend in data['friends']:
            db.session.add(Friend(ry=ry).update_from_dict(friend))


@app.route('/zw/info', methods=['PUT'])
@login_required(need_user=True)
def add_zw_info():
    data = request.get_json()
    if data is None:
        return jsonify_error(400, message='Require JSON data')

    today = date.today()
    department = g.user.department
    for item in data:
        entity = Zwinfo.query.filter_by(day=today, dw=department, lxdm=item['dm']).scalar()
        if entity is None:
            entity = Zwinfo(day=today, dw=department, lxdm=item['dm'], sl=item['sl'])
            db.session.add(entity)
        else:
            entity.sl = item['sl']

    db.session.commit()
    return success()


@app.route('/glds', methods=['GET'])
@login_required(roles=Roles.DEPARTMENT_USERS, need_user=True)
def query_all_gld():
    return jsonify([item.to_dict() for item in Gld.query.order_by(Gld.dm)])


@app.route('/gld/<dm>', methods=['PUT'])
@login_required(roles=Roles.DEPARTMENT_USERS, need_user=True)
def add_gld(dm):
    data = request.get_json()
    if data is None:
        return jsonify_error(400, message='Require JSON data')

    gld = Gld.query.filter_by(dm=dm).scalar()
    if gld is None:
        gld = Gld(dm=dm).update_from_dict(data)
        db.session.add(gld)
    else:
        check_department(gld.dwdm)
        gld.update_from_dict(data)

    db.session.commit()
    return success()


@app.route('/gld/<dm>', methods=['DELETE'])
@login_required(roles=Roles.DEPARTMENT_USERS, need_user=True)
def delete_gld(dm):
    gld = Gld.query.filter_by(dm=dm).scalar()
    if gld is not None:
        check_department(gld.dwdm)
        db.session.delete(gld)
        db.session.commit()
    return success()


@app.route('/xlsx/department/sheet-3-2', methods=['GET'])
@login_required(roles=Roles.DEPARTMENT_USERS)
def download_xlsx_sheet32():
    path = '/tmp/department-sheet-3-2__{}.xlsx'.format(datetime.now().strftime('%Y-%m-%d-%H-%M-%S'))
    sheet = SheetData(path)

    header_format1 = sheet.multi_span_header_format(row_span=2)
    header_format2 = sheet.multi_span_header_format(col_span=7)

    # write header
    row = 0
    col = 0
    for header in ['姓名', '部职别', '性别', '年龄', '住址', '确诊/疑似', '联系电话']:
        sheet.write(row, col, header, header_format1)
        col += 1

    sheet.write(row, col, '密切接触者', header_format2)
    for header in ['姓名', '部职别', '性别', '年龄', '住址', '联系电话', '与确诊或疑似者关系']:
        sheet.write_header(row + 1, col, header)
        col += 1

    sheet.write(row, col, '备注', header_format1)

    # write content
    row = 2

    qz_dict = {
        'N': '无',
        'Q': '疑似',
        'C': '确诊'
    }

    for ry in Ry.query.order_by(Ry.dwdm, Ry.sfzh):
        jczs = list(ry.jczs)
        if not len(jczs):
            continue

        ry_format = sheet.add_format(options={
            "align": 'center',
            "valign": 'vcenter'
        }, row_span=len(jczs))
        sheet.write(row, 0, ry.xm, ry_format)
        sheet.write(row, 1, ry.dwmc, ry_format)
        sheet.write(row, 2, ry.sex, ry_format)
        sheet.write(row, 3, ry.age, ry_format)
        sheet.write(row, 4, ry.zz, ry_format)
        sheet.write(row, 5, qz_dict.get(ry.ifqz, ''), ry_format)
        sheet.write(row, 6, ry.dh, ry_format)

        for jcz in jczs:
            sheet.write(row, 7, jcz.xm)
            sheet.write(row, 8, jcz.dwmc)
            sheet.write(row, 9, jcz.sex)
            sheet.write(row, 10, jcz.age)
            sheet.write(row, 11, jcz.zz)
            sheet.write(row, 12, jcz.dh)
            sheet.write(row, 13, jcz.gx)
            row += 1

    return sheet.send_file()


@app.route('/xlsx/department/sheet-1', methods=['GET'])
@login_required(roles=Roles.DEPARTMENT_USERS)
def download_excel_sheet01():
    path = '/tmp/department-sheet-1__{}.xlsx'.format(datetime.now().strftime('%Y-%m-%d-%H-%M-%S'))
    sheet = SheetData(path)

    header_format1 = sheet.multi_span_header_format(row_span=3)
    header_format2 = sheet.multi_span_header_format(row_span=2)

    # write headers
    sheet.write(0, 0, '序号', header_format1)
    sheet.write(0, 1, '单位', header_format1)

    sheet.write(0, 2, '当前隔离人数', sheet.multi_span_header_format(col_span=7))
    sheet.write(1, 2, '总人数', header_format2)
    sheet.write(1, 3, '密切接触者', sheet.multi_span_header_format(col_span=3))
    sheet.write_header(2, 3, '小计')
    sheet.write_header(2, 4, '集中隔离')
    sheet.write_header(2, 5, '居家隔离')
    sheet.write(1, 6, '返X人员', sheet.multi_span_header_format(col_span=3))
    sheet.write_header(2, 6, '小计')
    sheet.write_header(2, 7, '集中隔离')
    sheet.write_header(2, 8, '居家隔离')

    sheet.write(0, 9, '当日解除隔离人数', sheet.multi_span_header_format(col_span=3))
    sheet.write(1, 9, '总人数', header_format2)
    sheet.write(1, 10, '密切接触者', header_format2)
    sheet.write(1, 11, '返X人员', header_format2)
    sheet.write(0, 12, '累计解除隔离人数', sheet.multi_span_header_format(col_span=3))
    sheet.write(1, 12, '总人数', header_format2)
    sheet.write(1, 13, '密切接触者', header_format2)
    sheet.write(1, 14, '返X人员', header_format2)

    sheet.write(0, 15, '累计确诊人数', sheet.multi_span_header_format(col_span=4))
    sheet.write(1, 15, '小计', header_format2)
    sheet.write(1, 16, 'X队人员', header_format2)
    sheet.write(1, 17, 'X队人员家属', header_format2)
    sheet.write(1, 18, '其他人员', header_format2)

    sheet.write(0, 19, '当前疑似人数', sheet.multi_span_header_format(col_span=4))
    sheet.write(1, 19, '小计', header_format2)
    sheet.write(1, 20, 'X队人员', header_format2)
    sheet.write(1, 21, 'X队人员家属', header_format2)
    sheet.write(1, 22, '其他人员', header_format2)

    sheet.write(0, 23, '备注', header_format1)

    today = date.today()
    for i, department in enumerate(Dwdm.query.filter(Dwdm.dm.like('%00')).all()):
        row = i + 3
        sheet.write(row, 0, i + 1)
        sheet.write(row, 1, department.mc)

        prefix = department.dm[:2]
        ry_list = Ry.query.filter(Ry.dwdm.like('{}%'.format(prefix))).all()

        jcz_list = []
        for ry in ry_list:
            jcz_list.extend(ry.jczs)

        friend_list = []
        for ry in ry_list:
            friend_list.extend(ry.friends)

        isolation_jcz_home = 0
        isolation_jcz_office = 0
        isolation_ry_home = 0
        isolation_ry_office = 0

        un_isolation_jcz = 0
        un_isolation_ry = 0

        sum_un_isolation_jcz = 0
        sum_un_isolation_ry = 0

        sum_qz_ry = 0
        sum_qz_friend = 0
        sum_qz_other = 0

        sum_ys_ry = 0
        sum_ys_friend = 0
        sum_ys_other = 0

        for ry in ry_list:
            for xj in ry.xjinfos:
                # viper
                if xj.glqs_date and xj.glqs_date > today or xj.gljs_date and xj.gljs_date < today:
                    continue

                if xj.gljs_date == today:
                    un_isolation_ry += 1

                # confirm
                if xj.gljs_date and xj.gljs_date <= today:
                    sum_un_isolation_ry += 1

                if xj.glfs == '居家隔离':
                    isolation_ry_home += 1
                elif xj.glfs == '集中隔离':
                    isolation_ry_office += 1

            # TODO confirm
            if ry.ifqz == 'C':
                sum_qz_ry += 1
            elif ry.ifqz == 'Q':
                sum_ys_ry += 1

        for jcz in jcz_list:
            if jcz.ifgl:
                if jcz.glfs == '居家隔离':
                    isolation_jcz_home += 1
                elif jcz.glfs == '集中隔离':
                    isolation_jcz_office += 1

            if jcz.gljs_date:
                if jcz.gljs_date and jcz.gljs_date == today:
                    un_isolation_jcz += 1

                if jcz.gljs_date and jcz.gljs_date <= today:
                    sum_un_isolation_jcz += 1

            # TODO confirm
            if jcz.ifqz == 'C':
                sum_qz_ry += 1
            elif jcz.ifqz == 'Q':
                sum_ys_ry += 1

        for friend in friend_list:
            if friend.ifqz == 'C':
                if friend.sfzh is None:
                    sum_qz_other += 1
                else:
                    sum_qz_friend += 1
            elif friend.ifqz == 'Q':
                if friend.sfzh is None:
                    sum_ys_other += 1
                else:
                    sum_ys_friend += 1

        # 隔离人数
        sheet.write(row, 2, isolation_jcz_home + isolation_ry_office + isolation_ry_home + isolation_jcz_office)
        sheet.write(row, 3, isolation_jcz_home + isolation_jcz_office)
        sheet.write(row, 4, isolation_jcz_office)
        sheet.write(row, 5, isolation_jcz_home)
        sheet.write(row, 6, isolation_ry_home + isolation_ry_office)
        sheet.write(row, 7, isolation_ry_office)
        sheet.write(row, 8, isolation_ry_home)

        # 当日解除隔离人数
        sheet.write(row, 9, un_isolation_jcz + un_isolation_ry)
        sheet.write(row, 10, un_isolation_jcz)
        sheet.write(row, 11, un_isolation_ry)

        # 累计解除隔离人员
        sheet.write(row, 12, sum_un_isolation_jcz + sum_un_isolation_ry)
        sheet.write(row, 13, sum_un_isolation_jcz)
        sheet.write(row, 14, sum_un_isolation_ry)

        # 累计确诊人数
        sheet.write(row, 15, sum_qz_friend + sum_qz_other + sum_qz_ry)
        sheet.write(row, 16, sum_qz_ry)
        sheet.write(row, 17, sum_qz_friend)
        sheet.write(row, 18, sum_qz_other)

        # 当前疑似人数
        sheet.write(row, 19, sum_ys_friend + sum_ys_other + sum_ys_ry)
        sheet.write(row, 20, sum_ys_ry)
        sheet.write(row, 21, sum_ys_friend)
        sheet.write(row, 22, sum_ys_other)

    return sheet.send_file()


@app.route('/xlsx/department/sheet-2', methods=['GET'])
@login_required(roles=Roles.DEPARTMENT_USERS)
def download_excel_sheet02():
    path = '/tmp/department-sheet-2__{}.xlsx'.format(datetime.now().strftime('%Y-%m-%d-%H-%M-%S'))
    sheet = SheetData(path)

    # write headers
    sheet.write_header(0, 0, '序号', row_span=2)
    sheet.write_header(0, 1, '单位', row_span=2)
    sheet.write_header(0, 2, '合计', row_span=2)

    sheet.write_header(0, 3, '返队总人数', col_span=2)
    sheet.write_header(1, 3, '京内')
    sheet.write_header(1, 4, '京外')

    sheet.write_header(0, 5, '居家隔离人数', col_span=3)
    sheet.write_header(1, 5, '京内')
    sheet.write_header(1, 6, '京外')
    sheet.write_header(1, 7, '小计')

    sheet.write_header(0, 8, '集中隔离', col_span=5)
    sheet.write_header(1, 8, '京内点位数量')
    sheet.write_header(1, 9, '隔离人数')
    sheet.write_header(1, 10, '京外点位数量')
    sheet.write_header(1, 11, '隔离人数')
    sheet.write_header(1, 12, '小计')

    sheet.write_header(0, 13, '备注', row_span=2)

    today = date.today()
    for i, department in enumerate(Dwdm.query.filter(Dwdm.dm.like('%00')).all()):
        row = i + 2
        sheet.write(row, 0, i + 1)
        sheet.write(row, 1, department.mc)

        prefix = department.dm[:2]
        ry_list = Ry.query.filter(Ry.dwdm.like('{}%'.format(prefix))).all()

        sheet.write(row, 2, len(ry_list))

        in_home_beijing = 0
        in_office_beijing = 0
        out_home_beijing = 0
        out_office_beijing = 0

        for ry in ry_list:
            for xj in ry.xjinfos:
                if xj.glqs_date and xj.glqs_date > today or xj.gljs_date and xj.gljs_date < today:
                    continue

                if xj.glfs == '居家隔离':
                    if ry.dw.zdsf == '北京市':
                        in_home_beijing += 1
                    else:
                        out_home_beijing += 1
                elif xj.glfs == '集中隔离':
                    if xj.gld.dz.startswith('北京'):
                        in_office_beijing += 1
                    else:
                        out_office_beijing += 1
                break

        sheet.write(row, 5, in_home_beijing)
        sheet.write(row, 6, out_home_beijing)
        sheet.write(row, 7, in_home_beijing + out_home_beijing)

        gld_count_beijing = 0
        gld_count_not_beijing = 0
        for gld in Gld.query.all():
            if gld.dz.startswith('北京'):
                gld_count_beijing += 1
            else:
                gld_count_not_beijing += 1

        sheet.write(row, 8, gld_count_beijing)
        sheet.write(row, 9, in_office_beijing)
        sheet.write(row, 10, gld_count_not_beijing)
        sheet.write(row, 11, out_office_beijing)
        sheet.write(row, 12, in_office_beijing + out_office_beijing)

        sheet.write(row, 3, in_home_beijing + in_office_beijing)
        sheet.write(row, 4, out_home_beijing + out_office_beijing)
        sheet.write(row, 2, in_home_beijing + in_office_beijing + out_office_beijing + out_home_beijing)

    return sheet.send_file()


@app.route('/xlsx/department/sheet-3-1')
@login_required()
def download_xlsx_sheet31():
    path = '/tmp/department-sheet-3-1__{}.xlsx'.format(datetime.now().strftime('%Y-%m-%d-%H-%M-%S'))
    sheet = SheetData(path)

    header_format1 = sheet.add_format(sheet.default_header_format_options, row_span=2)
    header_format2 = sheet.add_format(sheet.default_header_format_options, col_span=7)
    header_format3 = sheet.add_format(sheet.default_header_format_options, col_span=3)
    header_format4 = sheet.add_format(sheet.default_header_format_options, col_span=2)

    # write header
    row = 0
    col = 0
    for header in ['序号', '单位', ]:
        sheet.write(row, col, header, header_format1)
        col += 1

    sheet.write(row, col, '确诊病例数', header_format2)
    col += 7
    sheet.write(row, col, '疑似病例数', header_format2)
    col += 7
    sheet.write(row, col, '密切接触者数', header_format2)
    col += 7
    sheet.write(row, col, '返京未满15总人数', header_format3)
    col += 3
    sheet.write_header(row, col, '未返京人数', col_span=2)
    col += 2
    sheet.write_header(row, col, '近2日人数', row_span=2)

    col = 2

    for header in ['老师', '学生', '助教', '离退休人员', '农村管理职工', '农村人员家属', '居住农村周边的其他人',
                   '老师', '学生', '助教', '离退休人员', '农村管理职工', '农村人员家属', '居住农村周边的其他人',
                   '老师', '学生', '助教', '离退休人员', '农村管理职工', '农村人员家属', '居住农村周边的其他人',
                   '住院观察数', '集中隔离数', '居家隔离数', '湖北地区', '全国其他地区']:
        sheet.write_header(row + 1, col, header)
        col += 1

    # write content
    row = 2

    qz_dict = {
        'N': '无',
        'Q': '疑似',
        'C': '确诊'
    }
    ls11 = 0
    ls12 = 0
    ls13 = 0
    ls14 = 0
    ls15 = 0
    ls16 = 0
    ls17 = 0

    ls21 = 0
    ls22 = 0
    ls23 = 0
    ls24 = 0
    ls25 = 0
    ls26 = 0
    ls27 = 0

    for ry in Ry.query.all():
        if ry.dw.zdsf == '北京':
            continue
        if ry.ifqz == 'C':
            if ry.lb == '老师':
                ls11 += 1
            elif ry.lb == '学生':
                ls12 += 1
            elif ry.lb == '助教':
                ls13 += 1
            elif ry.lb == '离休':
                ls14 += 1
            elif ry.lb == '职工':
                ls15 += 1

        if ry.ifqz == "Q":
            if ry.lb == '老师':
                ls21 += 1
            elif ry.lb == '学生':
                ls22 += 1
            elif ry.lb == '助教':
                ls23 += 1
            elif ry.lb == '离休':
                ls24 += 1
            elif ry.lb == '职工':
                ls25 += 1

    for friend in Friend.query.all():
        if friend.ifqz == 'C':
            if friend.sfzh is not None:
                ls16 += 1
            else:
                ls17 += 1

        if friend.ifqz == 'Q':
            ry = friend.ry
            if ry.sfzh is not None:
                ls26 += 1
            else:
                ls27 += 1
    ls31 = 0
    ls32 = 0
    ls33 = 0
    ls34 = 0
    ls35 = 0
    ls36 = 0
    ls37 = 0
    for jcz in Jcz.query.all():
        ry = jcz.ry
        if jcz.ry.dw.zdsf == '北京':
            continue
        if ry.lb == '老师':
            ls11 += 1
        elif ry.lb == '学生':
            ls12 += 1
        elif ry.lb == '助教':
            ls13 += 1
        elif ry.lb == '离休':
            ls14 += 1
        elif ry.lb == '职工':
            ls15 += 1
        elif ry.lb == '家属':
            ls16 += 1
        else:
            ls17 += 1

    ls41 = 0
    ls42 = 0
    ls43 = 0
    ls51 = 0
    ls52 = 0
    ls6 = 0
    for xjinfo in Xjinfo.query.all():
        # 隔离结束日期
        ry = xjinfo.ry
        dw = ry.dw
        if xjinfo.ry.dw.zdsf == '北京':
            continue

        if xjinfo.gljs_date and date.today().__le__(xjinfo.gljs_date):
            if xjinfo.glfs == '居家隔离':
                ls43 += 1
            if xjinfo.glfs == '集中隔离':
                ls42 += 1
            if xjinfo.glfs == '住院观察':
                ls41 += 1

        if xjinfo.ld_date and date.today().__sub__(xjinfo.ld_date).days < 2:
            ls6 += 1

    sheet.write(row, 0, '')
    sheet.write(row, 1, '合计')

    sheet.write(row, 2, ls11)
    sheet.write(row, 3, ls12)
    sheet.write(row, 4, ls13)
    sheet.write(row, 5, ls14)
    sheet.write(row, 6, ls15)
    sheet.write(row, 7, ls16)
    sheet.write(row, 8, ls17)

    sheet.write(row, 9, ls21)
    sheet.write(row, 10, ls22)
    sheet.write(row, 11, ls23)
    sheet.write(row, 12, ls24)
    sheet.write(row, 13, ls25)
    sheet.write(row, 14, ls26)
    sheet.write(row, 15, ls27)

    sheet.write(row, 16, ls31)
    sheet.write(row, 17, ls32)
    sheet.write(row, 18, ls33)
    sheet.write(row, 19, ls34)
    sheet.write(row, 20, ls35)
    sheet.write(row, 21, ls36)
    sheet.write(row, 22, ls37)

    sheet.write(row, 23, ls41)
    sheet.write(row, 24, ls42)
    sheet.write(row, 25, ls43)
    sheet.write(row, 26, ls51)
    sheet.write(row, 27, ls51)
    sheet.write(row, 28, ls6)

    row += 1

    return sheet.send_file()


# 4
@app.route('/xlsx/department/sheet-4')
@login_required()
def download_xlsx_sheet04():
    path = '/tmp/department-sheet-4__{}.xlsx'.format(datetime.now().strftime('%Y-%m-%d-%H-%M-%S'))
    sheet = SheetData(path)

    header_format0 = sheet.add_format(sheet.default_header_format_options)
    header_format2 = sheet.add_format(sheet.default_header_format_options, col_span=7)
    header_format13 = sheet.add_format(sheet.default_header_format_options, col_span=3)
    header_format31 = sheet.add_format(sheet.default_header_format_options, row_span=3)
    header_format21 = sheet.add_format(sheet.default_header_format_options, row_span=2)
    header_format12 = sheet.add_format(sheet.default_header_format_options, col_span=2)

    # write header
    row = 0
    col = 2
    for header in ['实有员额', '现在位人数', '在外休假人数', '异地湖北出差休假', '居家隔离', '集中隔离', '小计', '滞留湖北',
                   '实有员额', '现在位人数', '在外休假人数', '异地6省出差休假', '居家隔离', '集中隔离', '小计', '滞留6省',
                   '总人数', '居家隔离', '集中隔离', '小计', '解除人数', '新增人数']:
        sheet.write(row + 2, col, header, header_format0)
        col += 1

    row = 0
    col = 0
    sheet.write(row, col, '序号', header_format31)
    sheet.write(row, col + 1, '单位', header_format31)

    sheet.write(row, col + 2, '涉湖北地区学校、人员情况', header_format2)
    sheet.write(row, col + 9, '因疫情滞留湖北地区未归队的老师、学生人数', header_format31)
    sheet.write(row, col + 10, '涉疫情严重6省的学校、人员情况', header_format2)
    sheet.write(row, col + 17, '因疫情滞留6省地区未归队的老师、学生人数', header_format31)
    sheet.write(row, col + 18, '2月3日至2月19日异地归队人员情况', header_format2)

    sheet.write(row + 1, col + 2, '足湖北地区', header_format13)
    sheet.write(row + 1, col + 5, '异地湖北出差休假', header_format21)
    sheet.write(row + 1, col + 6, '湖北地区归队', header_format13)
    sheet.write(row + 1, col + 10, '足6省', header_format13)
    sheet.write(row + 1, col + 13, '异地6省出差休假', header_format21)
    sheet.write(row + 1, col + 14, '6省归队', header_format13)
    sheet.write(row + 1, col + 19, '任然在隔离', header_format13)
    #
    sheet.write(row + 1, col + 22, '当日隔离变化情况', header_format12)

    # write content
    row = 2

    qz_dict = {
        'N': '无',
        'Q': '疑似',
        'C': '确诊'
    }

    ls11 = 0
    ls12 = 0
    ls13 = 0
    ls14 = 0
    ls15 = 0
    ls16 = 0
    ls17 = 0
    ls18 = 0

    ls21 = 0
    ls22 = 0
    ls23 = 0
    ls24 = 0
    ls25 = 0
    ls26 = 0
    ls27 = 0
    ls28 = 0
    six_prov = ['广东', '河南', '浙江', '湖南', '安徽', '江西']
    for xjinfo in Xjinfo.query.all():
        if xjinfo.dz == '湖北':
            if xjinfo.gd_date and date.today().__le__(xjinfo.gd_date) and xjinfo.ry.ifxj:
                ls14 += 1

            if xjinfo.gljs_date and date.today().__le__(xjinfo.gljs_date):
                if xjinfo.glfs == '居家隔离':
                    ls15 += 1
                if xjinfo.glfs == '集中隔离':
                    ls16 += 1
                ls17 = ls15 + ls16

            if xjinfo.gd_date is None and xjinfo.ry.lb in ['老师', '学生']:
                ls18 += 1

        if xjinfo.dz in six_prov:
            if xjinfo.gd_date and date.today().__le__(xjinfo.gd_date) and xjinfo.ry.ifxj:
                ls24 += 1

            if xjinfo.gljs_date and date.today().__le__(xjinfo.gljs_date):
                if xjinfo.glfs == '居家隔离':
                    ls25 += 1
                if xjinfo.glfs == '集中隔离':
                    ls26 += 1
                ls27 = ls25 + ls26

            if xjinfo.gd_date is None and xjinfo.ry.lb in ['老师', '学生']:
                ls28 += 1

    for dwdm in Dwdm.query.all():
        if dwdm.zdsf in six_prov:
            ls21 = dwdm.gbsl + dwdm.zssl + dwdm.wzsl

    for ry in Ry.query.all():
        if ry.dw.zdsf in six_prov and ry.ifxj:
            ls23 += 1
    ls22 = ls21 - ls23

    ls31 = 0
    ls32 = 0
    ls33 = 0
    ls34 = 0
    ls35 = 0
    ls36 = 0
    for xjinfo in Xjinfo.query.all():
        # 归队时间为2月3日至2月19日
        t = xjinfo.gd_date
        start_day = datetime.today().date() + timedelta(days=-16)
        end_day = datetime.today().date() + timedelta(days=-1)
        if xjinfo.gd_date and start_day.__le__(xjinfo.gd_date) and end_day.__ge__(xjinfo.gd_date):
            ls31 += 1
            if xjinfo.gljs_date and date.today().__le__(xjinfo.gljs_date):
                if xjinfo.glfs == '居家隔离':
                    ls32 += 1
                if xjinfo.glfs == '集中隔离':
                    ls33 += 1
                ls34 = ls32 + ls33

        if xjinfo.gljs_date and date.today().__eq__(xjinfo.gljs_date):
            ls35 += 1

        if xjinfo.glqs_date and date.today().__eq__(xjinfo.glqs_date):
            ls36 += 1
    row = 4
    sheet.write(row, 2, ls11)
    sheet.write(row, 3, ls12)
    sheet.write(row, 4, ls13)
    sheet.write(row, 5, ls14)
    sheet.write(row, 6, ls15)
    sheet.write(row, 7, ls16)
    sheet.write(row, 8, ls17)
    sheet.write(row, 9, ls18)

    sheet.write(row, 10, ls21)
    sheet.write(row, 11, ls22)
    sheet.write(row, 12, ls23)
    sheet.write(row, 13, ls24)
    sheet.write(row, 14, ls25)
    sheet.write(row, 15, ls26)
    sheet.write(row, 16, ls27)
    sheet.write(row, 17, ls28)

    sheet.write(row, 18, ls31)
    sheet.write(row, 19, ls32)
    sheet.write(row, 20, ls33)
    sheet.write(row, 21, ls34)
    sheet.write(row, 22, ls35)
    sheet.write(row, 23, ls36)

    return sheet.send_file()


# 5
@app.route('/xlsx/department/sheet-5', methods=['GET'])
@login_required()
def download_xlsx_sheet05():
    path = '/tmp/department-sheet-5__{}.xlsx'.format(datetime.now().strftime('%Y-%m-%d-%H-%M-%S'))
    sheet = SheetData(path)

    header_format1 = sheet.add_format(sheet.default_header_format_options, row_span=2)
    header_format2 = sheet.add_format(sheet.default_header_format_options, col_span=2)

    # write header
    row = 0
    col = 0

    for header in ['序号', '单位', '足京单位', '目前在位', '总休假人数', '离京休假/疑似', '离京']:
        sheet.write(row, col, header, header_format1)
        col += 1

    sheet.write(row, col, '离京休假', header_format2)
    for header in ['已返回', '未返回']:
        sheet.write_header(row + 1, col, header)
        col += 1

    # write content
    row = 2

    qz_dict = {
        'N': '无',
        'Q': '疑似',
        'C': '确诊'
    }

    sl1_hj = 0
    sl2_hj = 0
    sl3_hj = 0
    sl4_hj = 0
    sl41_hj = 0
    sl42_hj = 0
    for dwdm in Dwdm.query.filter_by(zdsf='北京'):
        # 实有员额
        sl1 = dwdm.gbsl + dwdm.zssl + dwdm.wzsl + dwdm.zgsl
        # 在位

        # 休假总人数
        # 离京休假
        sl4 = 0
        sl41 = 0
        sl42 = 0
        rys = Ry.query.filter_by(dwdm=dwdm.dm, ifxj=True)
        day = date.today()
        for ry in rys:
            xjinfo = Xjinfo.query.filter_by(sfzh=ry.sfzh).scalar()
            if not xjinfo:
                continue
            if xjinfo.gd_date is None or date.today().__lt__(xjinfo.gd_date):
                sl42 += 1
            else:
                sl41 += 1
            sl4 += 1
        sl2 = sl1 - sl4

        sl1_hj += sl1
        sl2_hj += sl2
        sl4_hj += sl4
        sl41_hj += sl41
        sl42_hj += sl42

    sheet.write(row, 1, '合计', )
    sheet.write(row, 2, sl1_hj)
    sheet.write(row, 3, sl2_hj)
    # sheet.write(row, 3, sl3)
    sheet.write(row, 5, sl4_hj)
    sheet.write(row, 6, sl41_hj)
    sheet.write(row, 7, sl42_hj)

    return sheet.send_file()


# 6
@app.route('/xlsx/department/sheet-6', methods=['GET'])
@login_required(roles=Roles.DEPARTMENT_USERS, need_user=True)
def download_xlsx_sheet06():
    path = '/tmp/department-sheet-6__{}.xlsx'.format(datetime.now().strftime('%Y-%m-%d-%H-%M-%S'))
    sheet = SheetData(path)

    header_format1 = sheet.add_format(sheet.default_header_format_options, row_span=1)
    header_format2 = sheet.add_format(sheet.default_header_format_options, col_span=2)
    header_format6 = sheet.add_format(sheet.default_header_format_options, col_span=6)

    # write header
    row = 0
    col = 0
    for header in ['序号', '姓名', '部职别', '人员类别', '是否返回', '离京时间', '返回时间', '备注']:
        sheet.write(row, col, header, header_format1)
        col += 1

    sheet.write(row + 1, 0, '说明', header_format2)

    # write content
    row = 2

    xjinfos = Xjinfo.query.all()
    dwdms = Dwdm.query.order_by(Dwdm.dm)
    zwinfos = Zwinfo.query.order_by(Zwinfo.dwdm)

    tj_sy = 0
    tj_zw = 0

    tj_xj = 0
    tj_lj = 0
    tj_fh = 0
    tj_wfh = 0

    for xjinfo in xjinfos:
        ry = xjinfo.ry
        if xjinfo.ry.dw.zdsf != '北京':
            continue
        if g.auth.role == Roles.DEPARTMENT_MANAGER:
            dm_prefix = g.user.department.dm[:2]
            if xjinfo.ry.dwdm[:2] != dm_prefix:
                continue

        # 离京休假
        tj_lj += 1

        if date.today().__gt__(xjinfo.gd_date):
            # 已返回
            tj_fh += 1
        else:
            tj_wfh += 1

        sheet.write(row, 0, row - 1)
        sheet.write(row, 1, ry.xm)
        sheet.write(row, 2, ry.dwmc)
        sheet.write(row, 3, ry.lb)
        sheet.write(row, 4, ry.ifxj)
        sheet.write(row, 5, xjinfo.ld_date)
        sheet.write(row, 6, xjinfo.gd_date)
        sheet.write(row, 7, xjinfo.bz)

        row += 1

    for dwdm in dwdms:
        if dwdm.zdsf != '北京':
            continue
        if g.auth.role == Roles.DEPARTMENT_MANAGER:
            dm_prefix = g.user.department.dm[:2]
            if dwdm.dm[:2] != dm_prefix:
                continue
        else:
            # if dwdm.dm != g.user.department.dm:
            # TODO
            continue
        tj_sy += dwdm.gbsl + dwdm.zssl + dwdm.wzsl

    total_info1 = '1.足京单位实有额{}名，目前在位置人数{}名。'.format(tj_sy, tj_zw)
    total_info2 = '2.春节期间休假总人数{}名，其中离京休假人员{}名。'.format(tj_xj, tj_lj)
    total_info3 = '3.截至目前，离京城休假人员已经返回{}名，未返回{}名。'.format(tj_fh, tj_wfh)
    total_info = total_info1 + total_info2 + total_info3
    sheet.write(1, 2, total_info, header_format6)

    return sheet.send_file()


@app.route("/friends", methods=['GET'])
@login_required(roles=Roles.DEPARTMENT_USERS, need_user=True)
def query_friends():
    if g.auth.role == Roles.DEPARTMENT_MANAGER:
        dm_prefix = g.user.department.dm[:2]
        result = [item.to_dict() for item in Friend.query.filter(Friend.dwdm.like(dm_prefix + '%')).order_by(Friend.xm)]
    else:
        result = [item.to_dict() for item in Friend.query.order_by(Friend.xm)]
    return jsonify(result)


@app.route('/friend/<dm>', methods=['PUT'])
@login_required(roles=Roles.DEPARTMENT_USERS, need_user=True)
def add_friend(dm):
    data = request.get_json()
    if data is None:
        raise AabException(400, message='Require JSON data')

    # if g.auth.role == Roles.DEPARTMENT_MANAGER:
    #     dm_prefix = g.user.department.dm[:2]
    #     if not dm.startswith(dm_prefix):
    #         raise AabException(400, message='DM Forbidden')

    friend = Friend.query.filter_by(id=dm).scalar()
    if friend is None:
        friend = Friend().update_from_dict(data)
        db.session.add(friend)
    else:
        friend.update_from_dict(data)
    db.session.commit()
    return success()


def check_department(dm):
    if g.auth.role == Roles.DEPARTMENT_MANAGER:
        dm_prefix = g.user.department.dm[:2]
        if not dm.startswith(dm_prefix):
            raise AabException(400, message='DM Forbidden')


@app.route('/friend/<dm>', methods=['DELETE'])
@login_required(roles=Roles.DEPARTMENT_USERS, need_user=True)
def delete_friend(dm):
    check_department(dm)

    friend = Friend.query.filter_by(id=dm).scalar()
    if friend is not None:
        db.session.delete(friend)
        db.session.commit()

    return success()
